【新機能】Amazon Athenaでパラメータ変更できるSQLを実行可能になりました！

#Amazon Athena

#AWS

春田拓海 | Takumi

2021.07.07

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon Athenaで、SQLの再利用性・シンプル化・セキュリティの向上を強化するParameterized Queriesが新しく追加されました！

本記事で、概要と使ってみた様子をご紹介していきます。

Parameterized Queriesとは？

Parameterized Queriesとは、よく使用されるSQLをパラメータのみ変更して実行できる新機能です。これによって、Athena上で実行されている日々のワークロードを単純化できる他、SQLインジェクションに対する保護としても有効します。

Parameterized Queriesの実行には、事前にPrepared Statements（準備ステートメント）を作成する必要があります。Prepared Statementsには、パラメータ用のプレースホルダーが含まれ、PREPARE、EXECUTE、DEALLOCATE PREPAREの3種類のステートメントが用意されています。

PREPARE
- パラメータ用のプレースホルダーを組み込んだPrepared Statementsを定義する
- プレースホルダーには?を使用
- 複数パラメータも可能
EXECUTE
- Prepared Statementsにパラメータを組み込んで実行する
- プレースホルダーをパラメータで置換するにはUSINGを使用
DEALLOCATE PREPARE
- Prepared Statementsを削除する

制限

Prepared Statementsはワークグループ単位で保管され、Statementの名前はワークグループ内でユニークでなければならない
Athena engine version 2のみの対応
実行には、Prepared Statement用のIAM権限が必要
2021年7月7日現在、SELECT INSERT INTO CTAS のみ対応

実際に、まずはPrepared Statementsから作成していきます！

環境準備

サンプルテーブルとして、cm-harutaデータベース配下に公式で用意されているcloudfront_logsのテーブルを作成します。

Getting Started - Amazon Athena

CREATE EXTERNAL TABLE IF NOT EXISTS `cm-haruta`.`cloudfront_logs` (
  `Date` DATE,
  Time STRING,
  Location STRING,
  Bytes INT,
  RequestIP STRING,
  Method STRING,
  Host STRING,
  Uri STRING,
  Status INT,
  Referrer STRING,
  ClientInfo STRING
  ) 
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  LOCATION 's3://athena-examples-ap-northeast-1/cloudfront/plaintext/';

また、SQLを実行するワークグループはAthena engine version 2を指定しています。

実際に使ってみた！

まずはSELECT文でPrepared Statementsを作成してみます。

PREPARE test1 FROM
SELECT * FROM "cm-haruta"."cloudfront_logs"
WHERE status = ?
LIMIT 10

特に特殊なレスポンスはありませんでした。ちなみにですが、既に存在する名前でPrepared Statementsを作成すると、エラーやログもなく上書きされます。ここは要注意ですね。

作成したPrepared Statementsでパラメータを付与して実行してみます。

EXECUTE test1 USING 200

status = 200のレコードが無事出力されました。これは超便利！DEALLOCATE PREPAREで削除してみます。

DEALLOCATE PREPARE test1

こちらも特に特殊なレスポンスはありませんでした。消去したPrepared Statementsを実行しようとすると、当然ですがエラーが返されます。

PreparedStatement test1 was not found in workGroup cm-haruta

気になったのが、保管されているPrepared Statementsをどこから一覧で確認できるのかという点です。今のところAthenaのマネジメント・コンソールからはそれらしきタブやリンクが見つかりません。

ということでAWS CLIを見てみましたが、SDK側では実装済みでした！

SDKの方は一通りの機能が揃っているので、SQLよりかはSDK側で制御を行った方が安心ですね。

試しに適当にPrepared Statementsを追加して、list-prepared-statementsとget-prepared-statementを実行してみます。

$ aws athena list-prepared-statements --work-group cm-haruta
{
    "PreparedStatements": [
        {
            "StatementName": "test2",
            "LastModifiedTime": "2021-07-07T10:59:00.542000+09:00"
        },
        {
            "StatementName": "test1",
            "LastModifiedTime": "2021-07-07T10:58:19.893000+09:00"
        }
    ]
}

listではステートメント名と更新日が帰ってきます。test1の情報を取得してみます。

$ aws athena get-prepared-statement --statement-name test1 --work-group cm-haruta
{
    "PreparedStatement": {
        "StatementName": "test1",
        "QueryStatement": "SELECT *\nFROM\n  \"cm-haruta\".cloudfront_logs\nWHERE (status = ?)\nLIMIT 10\n",
        "WorkGroupName": "cm-haruta",
        "Description": "Created through SQL command.",
        "LastModifiedTime": "2021-07-07T10:58:19.893000+09:00"
    }
}

getではSQLの中身も返ってきますね。Descriptionとあるので、SDKで作成するとPrepared Statementsの概要も付与できそうです。

最後に、すでに存在しているステートメント名でcreate-prepared-statementを実行すると、ちゃんとエラーが出るのかどうか検証してみます。

$ aws athena create-prepared-statement --statement-name test1 --work-group cm-haruta --query-statement 'SELECT 1'
An error occurred (InvalidRequestException) when calling the CreatePreparedStatement operation: Prepared Statement test1 already exists in WorkGroup cm-haruta

エラーになりました！こういった点からも、SQLのPREPAREステートメントは使わずに、SDKで作成・更新・削除を行った方が安全かつ管理しやすいですね。